package com.gxa.wmcrm.service.Impl;

import com.gxa.wmcrm.entity.Employee;
import com.gxa.wmcrm.entity.EmployeeQueryCondition;
import com.gxa.wmcrm.entity.Page;
import com.gxa.wmcrm.service.EmployeeService;
import com.gxa.wmcrm.util.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class EmployeeServiceImpl implements EmployeeService {

    PreparedStatement ps = null;
    ResultSet rs = null;
    Connection conn = null;

    public EmployeeServiceImpl() {
    }

    @Override
    public List<Employee> queryEmployeeByPage(Page page) {
        List<Employee> list = null;
        try {
            // 调用工具类DBUtil，初始化数据库驱动，创建数据库连接
            Connection conn = DBUtil.getConn();
            //获取预编译集
            String sql = "select empId, empName, empSex, empBirth, empEducation, empTel, depotName, workName, empMajor, empAddress, empOutlook, empEntryTime from  employee limit ?, ?;";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, (page.getCurrentPage() - 1 )* page.getPerSize());
            ps.setInt(2, page.getPerSize());
            rs = ps.executeQuery();
            //获取数据集合
            list = new ArrayList<Employee>();
            while(rs.next()){
                Employee employee = new Employee();
                employee.setEmpId(rs.getInt("empId"));
                employee.setEmpName(rs.getString("empName"));
                employee.setEmpSex(rs.getString("empSex"));
                employee.setEmpBirth(rs.getDate("empBirth"));
                employee.setEmpEducation(rs.getString("empEducation"));
                employee.setEmpTel(rs.getString("empTel"));
                employee.setDepotName(rs.getString("depotName"));
                employee.setWorkName(rs.getString("workName"));
                employee.setEmpMajor(rs.getString("empMajor"));
                employee.setEmpAddress(rs.getString("empAddress"));
                employee.setEmpOutlook(rs.getString("empOutlook"));
                employee.setEmpEntryTime(rs.getDate("empEntryTime"));
                list.add(employee);
            }
        } catch (SQLException e) {
            e.getStackTrace();
        } finally {
            DBUtil.closeConn(conn, ps, null);
        }
        return list;
    }

    @Override
    public Integer updateEmployee(Employee employee) {
        int count = 0;
        try {
            // 调用工具类DBUtil，初始化数据库驱动，创建数据库连接
            Connection conn = DBUtil.getConn();
            //获取预编译集
            String sql = "update employee set empTel = ?, depotName = ?, workName = ? where empId = ?;";
            ps = conn.prepareStatement(sql);
            ps.setString(1, employee.getEmpTel());
            ps.setString(2, employee.getDepotName());
            ps.setString(3, employee.getWorkName());
            ps.setInt(4, employee.getEmpId());
            count = ps.executeUpdate();
        } catch (SQLException e) {
            e.getStackTrace();
        } finally {
            DBUtil.closeConn(conn, ps, null);
        }
        return count;
    }

    @Override
    public Integer deleteEmployee(Employee employee) {
        int count = 0;
        try {
            // 调用工具类DBUtil，初始化数据库驱动，创建数据库连接
            Connection conn = DBUtil.getConn();
            //获取预编译集
            String sql = "delete from employee where empId = ?;";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, employee.getEmpId());
            count = ps.executeUpdate();
        } catch (SQLException e) {
            e.getStackTrace();
        } finally {
            DBUtil.closeConn(conn, ps, null);
        };
        return count;
    }

    @Override
    public List<Employee> queryEmployeeByEmpName(EmployeeQueryCondition employeeQueryCondition) {
        List<Employee> list = null;
        try {
            // 调用工具类DBUtil，初始化数据库驱动，创建数据库连接
            Connection conn = DBUtil.getConn();
            //获取预编译集
            String sql = "select empId, empName, empSex, empBirth, empEducation, empTel, depotName, workName  from  employee where empName like ? ;";
            ps = conn.prepareStatement(sql);
            ps.setString(1, "%" + employeeQueryCondition.getValue() + "%");
            rs = ps.executeQuery();
            //获取数据集合
            list = new ArrayList<Employee>();
            while(rs.next()){
                Employee employee = new Employee();
                employee.setEmpId(rs.getInt("empId"));
                employee.setEmpName(rs.getString("empName"));
                employee.setEmpSex(rs.getString("empSex"));
                employee.setEmpBirth(rs.getDate("empBirth"));
                employee.setEmpEducation(rs.getString("empEducation"));
                employee.setEmpTel(rs.getString("empTel"));
                employee.setDepotName(rs.getString("depotName"));
                employee.setWorkName(rs.getString("workName"));
                list.add(employee);
            }
        } catch (SQLException e) {
            e.getStackTrace();
        } finally {
            DBUtil.closeConn(conn, ps, null);
        }
        return list;
    }

    @Override
    public Integer queryEmployeeCount() {
        int count = 0;
        try {
            // 调用工具类DBUtil，初始化数据库驱动，创建数据库连接
            Connection conn = DBUtil.getConn();
            //获取预编译集
            String sql = "select count(empId) from employee";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            //获取记录条数
            rs.next();
            count = rs.getInt(1);
        } catch (SQLException e) {
            e.getStackTrace();
        } finally {
            DBUtil.closeConn(conn, ps, null);
        }
        return count;
    }

    @Override
    public Integer addEmployee(Employee employee) {
        int count = 0;
        try {
            // 调用工具类DBUtil，初始化数据库驱动，创建数据库连接
            Connection conn = DBUtil.getConn();
            //获取预编译集
            String sql = "insert into employee(empName, empSex, empBirth, empEducation, empTel, depotName, workName) values(?,?,?,?,?,?,?);";
            ps = conn.prepareStatement(sql);
            ps.setString(1, employee.getEmpName());
            ps.setString(2,employee.getEmpSex());
            ps.setDate(3, (Date)employee.getEmpBirth());
            ps.setString(4, employee.getEmpEducation());
            ps.setString(5, employee.getEmpTel());
            ps.setString(6, employee.getDepotName());
            ps.setString(7, employee.getWorkName());
            count = ps.executeUpdate();
        } catch (SQLException e) {
            e.getStackTrace();
        } finally {
            DBUtil.closeConn(conn, ps, null);
        };
        return count;
    }

    @Override
    public Integer getEmpId(Page page, int index) {
        int count = 0;
        try {
            // 调用工具类DBUtil，初始化数据库驱动，创建数据库连接
            Connection conn = DBUtil.getConn();
            //获取预编译集
            String sql = "select empId from employee limit ?, 1";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,((page.getCurrentPage() - 1) * page.getPerSize() + index - 1));
            rs = ps.executeQuery();
            //获取所需EmpId
            rs.next();
            count = rs.getInt(1);
            System.out.println(count);
        } catch (SQLException e) {
            e.getStackTrace();
        } finally {
            DBUtil.closeConn(conn, ps, null);
        }
        return count;
    }
}
